using System;
using System.Collections.Generic;
using System.Text;
using student_factory.Metier;
using System.Data.SqlClient;

namespace student_factory.DAO
{
    class MatiereDAO
    {
        private static String connection = "data source=localhost;initial catalog=STUDENTS_FACTORY;User ID=sa;Password=";

        public static IList<Matiere> getMatieresFromEtudiant(Etudiant etu)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            IList<Matiere> matieres = new List<Matiere>();

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "SELECT M.ID_M as ID_M, M.MAT_DESCRIPTION as MAT_DESCRIPTION from SUIVRE S, MATIERES M WHERE S.ID_M like M.ID_M AND S.ID_E like '"+etu.Id_e+"'";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {

                    string id = reader["ID_M"].ToString();
                    string nom = reader["MAT_DESCRIPTION"].ToString();
                    Matiere mat = new Matiere(id,nom);
                    matieres.Add(mat);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return matieres;
        }

        public static String getAnneeSuiviMatiere(Etudiant etudiant, Matiere matiere)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            string annee = "1664";

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "SELECT SUI_ANNEE from SUIVRE WHERE ID_E like '" + etudiant.Id_e + "' and ID_M like '"+matiere.Id_m+"'";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    annee = reader["SUI_ANNEE"].ToString();
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return annee;
        }

        public static bool isMatiereValide(Etudiant etudiant, Matiere matiere)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            string valid = "0";

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "SELECT SUI_CONFIRMATION from SUIVRE WHERE ID_E like '" + etudiant.Id_e + "' and ID_M like '" + matiere.Id_m + "'";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    valid = reader["SUI_CONFIRMATION"].ToString();
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return Boolean.Parse(valid);
        }

        public static void setMatiereConfirmation(Etudiant etudiant, Matiere matiere, bool confirm)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            string valid = confirm ? "1" : "0";

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "UPDATE SUIVRE SET SUI_CONFIRMATION = " + valid + 
                    " WHERE ID_E like '" + etudiant.Id_e + "' and ID_M like '" + matiere.Id_m + "'";

                reader = commande.ExecuteReader();

            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

        }

    }
}
